package com.eoekun.dashboard.util;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.Region;
import jxl.Cell;
import jxl.CellType;
import jxl.CellView;
import jxl.DateCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Alignment;
import jxl.write.Border;
import jxl.write.BorderLineStyle;
import jxl.write.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

@SuppressWarnings({"deprecation", "rawtypes", "unchecked"})
public class ExcelUtil {
    private static Logger logger = Logger.getLogger(ExcelUtil.class);

    /**
     * 
     * @Description: 导出电子表格
     * @param
     * @return String
     * @throws @author chenkun
     * @createtime 2016年07月05日 下午10:51:28
     */
    public static String exportJXLWorkbook(List<String> fieldNameList, List<String> fieldIndexList,
            List<Map> dataList, String fileName, HttpServletRequest request,
            HttpServletResponse response) {
        try {
            OutputStream out = response.getOutputStream();
            // 清空输出流
            response.reset();
            if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
                fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1");// firefox浏览器
            } else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {
                fileName = URLEncoder.encode(fileName, "UTF8");// IE浏览器
            } else {
                fileName = URLEncoder.encode(fileName, "UTF8");// IE浏览器
            }
            // 设置响应头和下载保存的文件名
            response.setHeader("content-disposition", "attachment;filename=" + fileName);
            // 定义输出类型
            response.setContentType("APPLICATION/msexcel");
            // String[] headers = new String[fieldNameList.size()+2];

            // 创建工作薄,一个参数
            WritableWorkbook wb = Workbook.createWorkbook(out);
            // 创建工作表
            WritableSheet sheet = wb.createSheet("Sheet1", 0);
            wb.createSheet("Sheet2", 1);
            wb.createSheet("Sheet3", 2);
            if (dataList.size() < 60000) {
                Label label;
                // 填充表头
                for (int i = 0; i < fieldNameList.size(); i++) {
                    CellView cellView = new CellView();
                    sheet.setColumnView(i, cellView);
                    // Label(x,y,z)其中x代表单元格的第x+1列，y代表单元格的第y+1行, 单元格的内容是z
                    sheet.setColumnView(i, 12);
                    label = new Label(i, 0, fieldNameList.get(i));
                    sheet.addCell(label);
                }
                for (int row = 0; row < dataList.size(); row++) {
                    for (int col = 0; col < fieldIndexList.size(); col++) {
                        Object value = "";

                        value = (dataList.get(row)).get(fieldIndexList.get(col));
                        sheet.addCell(new Label(col, row + 1,
                                (null == value || value == "null") ? "" : String.valueOf(value)));
                        // sheet.addCell(new Label(col, index + 1,
                        // String.valueOf(value != null ? value : "")));
                    }
                }
                // 定义字体等样式
                WritableFont headFont = new WritableFont(WritableFont.createFont("宋体"), 12);
                WritableCellFormat wcf2 = new WritableCellFormat(headFont);
                wcf2.setAlignment(Alignment.CENTRE);
                wcf2.setBorder(Border.ALL, BorderLineStyle.THIN);
                wcf2.setBackground(Colour.GRAY_25);
                WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10);
                WritableCellFormat wfont = new WritableCellFormat(font);
                wfont.setAlignment(Alignment.CENTRE);
                // 这种方式是填充数据后设置的样式
                for (int index = 0; index < dataList.size() + 1; index++) {
                    for (int j = 0; j < fieldNameList.size(); j++) {
                        String content = sheet.getCell(j, index).getContents();
                        if (index == 0) {
                            sheet.addCell(new Label(j, index, content, wcf2));
                        } else {
                            sheet.addCell(new Label(j, index, content, wfont));
                        }
                    }
                }
            } else {
                int sheetCount = dataList.size() / 60000 + 1;
                if (sheetCount > 3) {
                    for (int i = 4; i <= sheetCount; i++) {
                        wb.createSheet("Sheet" + i, i - 1);
                    }
                }
                for (int ii = 0; ii < sheetCount; ii++) {
                    Label label;
                    WritableSheet sheet2 = wb.getSheet(ii);
                    // 填充表头
                    for (int i = 0; i < fieldNameList.size(); i++) {
                        CellView cellView = new CellView();
                        sheet2.setColumnView(i, cellView);
                        sheet2.setColumnView(i, 12);
                        label = new Label(i, 0, fieldNameList.get(i));
                        sheet2.addCell(label);
                    }
                    List<Map> datas = new ArrayList<>();
                    int fromIndex = ii * 60000;
                    int toIndex = (ii + 1) * 60000 - 1;
                    if (toIndex > dataList.size()) {
                        toIndex = dataList.size();
                    }
                    datas = dataList.subList(fromIndex, toIndex);
                    for (int row = 0; row < datas.size(); row++) {
                        for (int col = 0; col < fieldIndexList.size(); col++) {
                            Object value = "";
                            value = (datas.get(row)).get(fieldIndexList.get(col));
                            sheet2.addCell(
                                    new Label(col, row + 1, (null == value || value == "null") ? ""
                                            : String.valueOf(value)));
                        }
                    }
                    // 定义字体等样式
                    WritableFont headFont = new WritableFont(WritableFont.createFont("宋体"), 12);
                    WritableCellFormat wcf2 = new WritableCellFormat(headFont);
                    wcf2.setAlignment(Alignment.CENTRE);
                    wcf2.setBorder(Border.ALL, BorderLineStyle.THIN);
                    wcf2.setBackground(Colour.GRAY_25);
                    WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10);
                    WritableCellFormat wfont = new WritableCellFormat(font);
                    wfont.setAlignment(Alignment.CENTRE);
                    // 这种方式是填充数据后设置的样式
                    for (int index = 0; index < datas.size() + 1; index++) {
                        for (int j = 0; j < fieldNameList.size(); j++) {
                            String content = sheet.getCell(j, index).getContents();
                            if (index == 0) {
                                sheet2.addCell(new Label(j, index, content, wcf2));
                            } else {
                                sheet2.addCell(new Label(j, index, content, wfont));
                            }
                        }
                    }
                }

            }

            // 进行写操作
            wb.write();
            wb.close();
        } catch (FileNotFoundException e) {
            logger.info(e);
        } catch (IOException e) {
            logger.info(e);
        } catch (RowsExceededException e) {
            e.printStackTrace();
            logger.info(e);
        } catch (WriteException e) {
            logger.info(e);
        }
        return null;
    }

    /**
     * 
     * @Description: 获取excel文件
     * @param
     * @return String
     * @throws @author chenkun
     * @createtime 2016年07月05日 下午10:51:28
     */
    public static String exportJXLWorkbook2(List<String> fieldNameList, List<String> fieldIndexList,
            List<Map> dataList, String fileName) {
        try {
            FileOutputStream out = new FileOutputStream(fileName);
            // 创建工作薄,一个参数
            WritableWorkbook wb = Workbook.createWorkbook(out);
            // 创建工作表
            WritableSheet sheet = wb.createSheet("Sheet1", 0);
            wb.createSheet("Sheet2", 1);
            wb.createSheet("Sheet3", 2);
            Label label;
            // 填充表头
            for (int i = 0; i < fieldNameList.size(); i++) {
                CellView cellView = new CellView();
                sheet.setColumnView(i, cellView);
                sheet.setColumnView(i, 12);
                label = new Label(i, 0, fieldNameList.get(i));
                sheet.addCell(label);
            }

            for (int row = 0; row < dataList.size(); row++) {
                for (int col = 0; col < fieldIndexList.size(); col++) {
                    Object value = "";
                    value = (dataList.get(row)).get(fieldIndexList.get(col));
                    sheet.addCell(new Label(col, row + 1,
                            (null == value || value == "null") ? "" : String.valueOf(value)));
                }
            }
            // 定义字体等样式
            WritableFont headFont = new WritableFont(WritableFont.createFont("宋体"), 12);
            WritableCellFormat wcf2 = new WritableCellFormat(headFont);
            wcf2.setAlignment(Alignment.CENTRE);
            wcf2.setBorder(Border.ALL, BorderLineStyle.THIN);
            wcf2.setBackground(Colour.GRAY_25);
            WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 10);
            WritableCellFormat wfont = new WritableCellFormat(font);
            wfont.setAlignment(Alignment.CENTRE);
            // 这种方式是填充数据后设置的样式
            for (int index = 0; index < dataList.size() + 1; index++) {
                for (int j = 0; j < fieldNameList.size(); j++) {
                    String content = sheet.getCell(j, index).getContents();
                    if (index == 0) {
                        sheet.addCell(new Label(j, index, content, wcf2));
                    } else {
                        sheet.addCell(new Label(j, index, content, wfont));
                    }
                }
            }
            // 进行写操作
            wb.write();
            wb.close();
            out.close();
        } catch (FileNotFoundException e) {
            logger.info(e);
        } catch (IOException e) {
            logger.info(e);
        } catch (RowsExceededException e) {
            logger.info(e);
        } catch (WriteException e) {
            logger.info(e);
        }
        return null;
    }

    public static <T> InputStream outPut(List<ColumnInfo> columnInfos, List<T> datas) {
        InputStream inputStream = null;
        try {
            Map<Integer, List<ColumnInfo>> rowToColumns = new HashMap<Integer, List<ColumnInfo>>();
            for (ColumnInfo column : columnInfos) {
                int row = column.getStartRow();
                List<ColumnInfo> columnList = rowToColumns.get(row);
                if (columnList == null) {
                    columnList = new ArrayList<ColumnInfo>();
                    rowToColumns.put(row, columnList);
                }
                columnList.add(column);
            }
            // 创建一个工作薄
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet1 = wb.createSheet("导出结果");
            HSSFCellStyle cellStyle = getHeaderCellStyle(wb);
            List<Integer> rowList = new ArrayList<Integer>(rowToColumns.keySet());
            Collections.sort(rowList);
            for (Integer rowIndex : rowList) {
                List<ColumnInfo> columnInfoList = rowToColumns.get(rowIndex);
                HSSFRow headerRow = sheet1.createRow(rowIndex);
                for (ColumnInfo columnInfo : columnInfoList) {
                    sheet1.addMergedRegion(
                            new Region(columnInfo.getStartRow(), columnInfo.getStartColumn(),
                                    columnInfo.getEndRow(), columnInfo.getEndColumn()));
                    HSSFCell cell = headerRow.createCell(columnInfo.getStartColumn());
                    cell.setCellStyle(cellStyle);

                    String text = columnInfo.getText().replaceAll("<br>", "")
                            .replaceAll("<br/>", "").replaceAll("<BR>", "").replaceAll("<BR/>", "");
                    cell.setCellValue(text);
                    sheet1.setColumnWidth(columnInfo.getStartColumn(), (short) (28 * 260));
                }
            }
            int startRow = rowList.size();
            boolean isMap = true;
            try {
                isMap = datas.get(0) instanceof Map;
            } catch (Exception e) {
                e.printStackTrace();
            }
            HSSFCellStyle style = getCellStyle(wb);
            for (T data : datas) {
                HSSFRow headerRow = sheet1.createRow(startRow++);
                for (ColumnInfo columnInfo : columnInfos) {

                    if (columnInfo.isDataColumn()) {

                        String value = "";
                        Object objValue = null;
                        if (isMap) {
                            Map rowMap = (Map) data;
                            objValue = rowMap.get(columnInfo.getDataIndex());
                        } else {
                            try {
                                Field field =
                                        data.getClass().getDeclaredField(columnInfo.getDataIndex());
                                field.setAccessible(true);
                                objValue = field.get(data);
                            } catch (Exception e) {
                                e.printStackTrace();
                            }
                        }

                        if (objValue instanceof Date) {
                            value = new SimpleDateFormat("yyy-MM-dd hh:mm:ss").format(objValue);
                        } else {
                            value = String.valueOf(objValue);
                        }
                        if ("null".equals(value)) {
                            value = "";
                        }
                        HSSFCell cell = headerRow.createCell(columnInfo.getStartColumn());
                        cell.setCellValue(value);
                        cell.setCellStyle(style);
                    }
                }
            }
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 255, 255, (short) 0,
                    datas.size() + 2, (short) 6, datas.size() + 17);
            anchor.setAnchorType(3);
            ByteArrayOutputStream resultArrayOutputStream = new ByteArrayOutputStream();
            wb.write(resultArrayOutputStream);
            inputStream = new ByteArrayInputStream(resultArrayOutputStream.toByteArray());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return inputStream;
    }

    public static HSSFCellStyle getHeaderCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle setBorder = wb.createCellStyle();
        // 设置背景色：
        setBorder.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置背景色
        setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // 设置边框:
        setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        setBorder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        // 设置居中:
        setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        // 设置字体:
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setBoldweight((short) 13);
        font.setFontHeightInPoints((short) 13);// 设置字体大小
        setBorder.setFont(font);// 选择需要用到的字体格式
        return setBorder;
    }

    public static HSSFCellStyle getCellStyle(HSSFWorkbook wb) {
        HSSFCellStyle setBorder = wb.createCellStyle();
        // 设置背景色：
        setBorder.setFillForegroundColor(HSSFColor.WHITE.index);// 设置背景色
        setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // 设置边框:
        setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
        setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        setBorder.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
        // 设置居中:
        setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
        // 设置字体:
        HSSFFont font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);// 设置字体大小
        setBorder.setFont(font);// 选择需要用到的字体格式
        return setBorder;
    }

    // 去读Excel的方法readExcel，该方法的入口参数为一个File对象
    public static List readExcel(File file, String dateFormat) {
        try {
            // 创建输入流，读取Excel
            InputStream is = new FileInputStream(file.getAbsolutePath());
            // jxl提供的Workbook类
            Workbook wb = Workbook.getWorkbook(is);
            // Excel的页签数量
            int sheet_size = wb.getNumberOfSheets();
            int maxLine = 1;
            for (int index = 0; index < sheet_size;) {
                List<List> outerList = new ArrayList<List>();
                // 每个页签创建一个Sheet对象
                Sheet sheet = wb.getSheet(index);
                // sheet.getRows()返回该页的总行数
                for (int i = 0; i < sheet.getRows(); i++) {
                    List innerList = new ArrayList();
                    // sheet.getColumns()返回该页的总列数
                    for (int j = 0; j < sheet.getColumns(); j++) {
                        Cell cell = sheet.getCell(j, i);
                        String cellinfo = cell.getContents();
                        if (cell.getType() == CellType.DATE) {
                            DateCell dc = (DateCell) cell;
                            Date date = dc.getDate();
                            // Calendar c = Calendar.getInstance();
                            // c.setTime(date);
                            // // 解析excel的时候会默认当前输入的时间为格林威治时间，需要转为当前时区的时间（之前8小时）
                            // c.add(Calendar.HOUR, -8);
                            cellinfo = new SimpleDateFormat(dateFormat).format(date);
                        }
                        if (cellinfo.isEmpty() && j > maxLine) {
                            continue;
                        }
                        maxLine = Math.max(maxLine, j);
                        innerList.add(cellinfo);
                    }
                    outerList.add(i, innerList);
                }
                return outerList;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

}
